package dataHandlers.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.LinkedList;






import clientData.ForumData;
import clientData.ForumDataInt;
import clientData.MemberData;
import clientData.MemberDataInt;
import clientData.PostData;
import clientData.PostDataInt;
import clientData.TopicData;
import clientData.TopicDataInt;
import dataHandlers.DataControlerInt;
import exception.ForumNotFoundException;
import exception.PostNotExistException;
import exception.TopicNotFoundException;


public class DBDataControler implements DataControlerInt {
	
	private static DataControlerInt singletonObject;
    private Connection conn;
    
    private DBDataControler(){
    	String userName = "root";
        String password = "sadna";
        String url = "jdbc:mysql://localhost:3306/sadna";
	    try
	    {
	    	//Class.forName ("com.mysql.jdbc.exceptions.jdbc4.CommunicationsException").newInstance();
	        Class.forName ("com.mysql.jdbc.Driver").newInstance();
	        this.conn = DriverManager.getConnection (url, userName, password);
	    }
	    catch (Exception e)
	    {
	        System.err.println ("Cannot connect to database server");
	        System.err.println (e.getMessage());
	    }
    }

	public static DataControlerInt getDataControler() {
		if (singletonObject == null) {
			singletonObject = new DBDataControler();
		}
		return singletonObject;
	}
	
	@Override
	public int addForum(String userName, String forumName) throws SQLException {
		try
		{
		int id = -1;
			Statement s = conn.createStatement();
			String sqlStatement = "INSERT INTO `sadna`.`forums` (`name`, `creator`) VALUES('"+forumName+"','"+userName+"')";

			//s = conn.prepareStatement ("INSERT INTO `sadna`.`forums` (`name`, `creator`) VALUES(?,?)");
		//	s.setString (1, forumName);
		//	s.setString (2, userName);
			//s.executeUpdate();
			s.executeUpdate(sqlStatement, Statement.RETURN_GENERATED_KEYS);
			ResultSet rs = s.getGeneratedKeys();
			rs.first();
			id = Integer.parseInt(rs.getString(1));
			rs.close();
			s.close();
		return id;	
		}
		catch(SQLException e){throw new SQLException();}
	}

	@Override
	public void addFriend(String userName, String friendUserName) throws SQLException {
		try
		{
		PreparedStatement s;
			s = conn.prepareStatement ("INSERT INTO `sadna`.`friends` (`username`, `friends_username`) VALUES(?,?)");
			s.setString (1, userName);
			s.setString (2, friendUserName);
			s.executeUpdate();
			//ResultSet rs = s.getResultSet(); //eror
			//System.out.println(rs.getString(1));//eror
			s.close();
		}
		catch(SQLException e){throw new SQLException();}
	}
	
	@Override
	public void addMember(String userName, int id, String email, String firstName, String lastName) throws SQLException {
		try
		{
		PreparedStatement s;
			s = conn.prepareStatement ("INSERT INTO `sadna`.`members` (`id`, `userName`, `email`, `firstName`, `lastName`) VALUES (?, ?, ?, ?, ?)");
			s.setInt(1, id);
			s.setString (2, userName);
			s.setString (3, email);
			s.setString (4, firstName);
			s.setString (5, lastName);
			s.executeUpdate ();
			s.close();		
		}
		catch(SQLException e){throw new SQLException();}
	}

	@Override
	public int addPost(String userName, int forumId, int topicId, String postTitle, String postContent) throws SQLException {
		
		try{
		int id = -1;
		
			Date time = new Date();
			long javaTime = time.getTime();	
			java.sql.Timestamp sqlTimestamp = new java.sql.Timestamp(javaTime);
			
			
			Statement s = conn.createStatement();
			String sqlStatement = "INSERT INTO `sadna`.`posts` (`title`, `content`, `creationTime`, `editingTime`, `creator`, `topicId`)"+
								  " VALUES ('"+postTitle+"', '"+postContent+"', '"+sqlTimestamp.toString()+"', '"+sqlTimestamp.toString()+"', '"+userName+"', '"+ topicId+"')";
			//s = conn.prepareStatement ("INSERT INTO `sadna`.`forums` (`name`, `creator`) VALUES(?,?)");
		//	s.setString (1, forumName);
		//	s.setString (2, userName);
			//s.executeUpdate();
			s.executeUpdate(sqlStatement, Statement.RETURN_GENERATED_KEYS);
			ResultSet rs = s.getGeneratedKeys();
			rs.first();
			id = Integer.parseInt(rs.getString(1));
			rs.close();
			s.close();
			
			
			
		//	PreparedStatement s;
		//	s = conn.prepareStatement ("INSERT INTO `sadna`.`posts` (`title`, `content`, `creationTime`, `editingTime`, `creator`, `topicId`) VALUES (?, ?, ?, ?, ?, ?)");
		//	s.setString (1, postTitle);
		//	s.setString (2, postContent);
			
		//	s.setTimestamp(3,(Timestamp) time);// creation time
		//	s.setTimestamp(4,(Timestamp) time);// editing time
		//	s.setString (5, userName);
		//	s.setInt(6, topicId);
		//	s.ex
		//	s.executeUpdate ();
		//	s.close();
			
		return id;
		}
		catch(SQLException e){throw new SQLException();}
	}

	@Override
	public int addTopic(String userName, int forumId, String topicTitle) throws SQLException {
		
		try{
		int id = -1;
			Date time = new Date();
			long javaTime = time.getTime();	
			java.sql.Timestamp sqlTimestamp = new java.sql.Timestamp(javaTime);
			
			Statement s = conn.createStatement();
			String sqlStatement = "INSERT INTO `sadna`.`topics` (`title`, `creationTime`, `creator`, `forumId`) "+
								  "VALUES ('"+topicTitle+"','"+sqlTimestamp.toString()+"','"+ userName+"','"+forumId+"')";

			//s = conn.prepareStatement ("INSERT INTO `sadna`.`forums` (`name`, `creator`) VALUES(?,?)");
		//	s.setString (1, forumName);
		//	s.setString (2, userName);
			//s.executeUpdate();
			s.executeUpdate(sqlStatement, Statement.RETURN_GENERATED_KEYS);
			ResultSet rs = s.getGeneratedKeys();
			rs.first();
			id = Integer.parseInt(rs.getString(1));
			rs.close();
			s.close();
		
		//	PreparedStatement s;
		//	s = conn.prepareStatement ("INSERT INTO `sadna`.`topics` (`title`, `creationTime`, `creator`, `forumId`) VALUES (?,?,?,?)");
		//	s.setString (1, topicTitle);
		//	Date time = new Date();
		//	s.setTimestamp(2,(Timestamp) time);// creation time
		//	s.setString (3, userName); // creator
		//	s.setInt(4, forumId);
		//	s.executeUpdate ();
		//	s.close();
			
		return id;
		}
		catch(SQLException e){throw new SQLException();}
	}

	@Override
	public void editPost(int oldPostId, int forumId, int topicId, String newContent, String newTitle) throws SQLException {
			
		try{
		PreparedStatement s;
			s = conn.prepareStatement ("UPDATE `sadna`.`posts` SET `content`=?,`title`=?,`editingTime`=? WHERE `id`=?");
			Date time = new Date();
			long javaTime = time.getTime();	
			java.sql.Timestamp sqlTimestamp = new java.sql.Timestamp(javaTime);
			
			
			s.setString (1, newContent);
			s.setString (2, newTitle);
			
			
			
			s.setTimestamp(3,sqlTimestamp);// editing time
			s.setInt(4, oldPostId);
			s.executeUpdate ();
			s.close();
		}
		catch(SQLException e){throw new SQLException();}
	}

	@Override
	public LinkedList<ForumDataInt> getForums() throws SQLException {
		try{
		LinkedList<ForumDataInt> ans = new LinkedList<ForumDataInt>();
			Statement s = conn.createStatement();
			s.executeQuery("SELECT id, name, creator FROM sadna.forums");
			ResultSet rs = s.getResultSet();
			while (rs.next())
			{		
				int forumId = rs.getInt ("id");
				String forumName = rs.getString ("name");
				String creatorUserName = rs.getString ("creator");
				Statement sCreator = conn.createStatement();
				sCreator.executeQuery("SELECT id, userName, email, firstName, lastName FROM sadna.members WHERE userName ='"+creatorUserName+"'");
				ResultSet rsCreator = sCreator.getResultSet();
				rsCreator.first();
				int creatorId = rsCreator.getInt ("id");
				String creatorEmail = rsCreator.getString ("email");
				String creatorfName = rsCreator.getString ("firstName");
				String creatorlName = rsCreator.getString ("lastName");
				MemberDataInt creator = new MemberData(creatorId, creatorUserName, creatorEmail, creatorfName, creatorlName);
				ForumDataInt fd = new ForumData(forumId, forumName, creator);
				ans.add(fd);
				rsCreator.close();
				sCreator.close();
			}
			rs.close ();
			s.close ();
		return ans;
		
		}
		catch(SQLException e){throw new SQLException();}
	}

	@Override
	public LinkedList<MemberDataInt> getFriends(String userName) throws SQLException {
		
		try
		{
		
		LinkedList<MemberDataInt> ans = new LinkedList<MemberDataInt>();
			Statement s = conn.createStatement();
			s.executeQuery("SELECT friends_username FROM sadna.friends WHERE username ='"+userName+"'");
			ResultSet rs = s.getResultSet();
			while (rs.next())
			{	
				String friendUserName = rs.getString("friends_username");
				Statement sFriend = conn.createStatement();
				sFriend.executeQuery("SELECT id, userName, email, firstName, lastName FROM sadna.members WHERE userName ='"+friendUserName+"'");
				//System.out.println("was here1");
				ResultSet rsFriend = sFriend.getResultSet();
				rsFriend.first();
				//System.out.println(rsFriend.first());
				//System.out.println("was here2");
				int friendId = rsFriend.getInt ("id");
				String friendEmail = rsFriend.getString ("email");
				String friendfName = rsFriend.getString ("firstName");
				String friendlName = rsFriend.getString ("lastName");
				//System.out.println("was here3");
				//System.out.println(creatorId+" "+creatorEmail+" "+creatorfName+" "+creatorlName);
				MemberDataInt friend = new MemberData(friendId, friendUserName, friendEmail, friendfName, friendlName);
				ans.add(friend);
				rsFriend.close();
				sFriend.close();
			}
			rs.close ();
			s.close ();
		return ans;
		}
		catch(SQLException e){throw new SQLException();}
	}
	

	//*************************************** Marina ***************************  
	@Override
	public LinkedList<MemberDataInt> getMembersByPrefix(String userNamePrefix) throws SQLException {
		
		try{
		LinkedList<MemberDataInt> ans = new LinkedList<MemberDataInt>();
			Statement s = conn.createStatement();
			s.executeQuery("SELECT id, userName, email, firstName, lastName FROM sadna.members WHERE userName LIKE '"+userNamePrefix+"%'");
			ResultSet rs = s.getResultSet();
			while (rs.next())
			{	
				int ID = rs.getInt("id");
				String username = rs.getString("userName");
				String email = rs.getString ("email");
				String fName = rs.getString ("firstName");
				String lName = rs.getString ("lastName");
				
				MemberDataInt member = new MemberData(ID, username, email, fName, lName);
				ans.add(member);
			}
			rs.close ();
			s.close ();
		return ans;
		}
		catch(SQLException e){throw new SQLException();}
	}
	
	@Override
	public LinkedList<PostDataInt> getPosts(int forumId, int topicId) throws SQLException {
		
		try{
		LinkedList<PostDataInt> ans = new LinkedList<PostDataInt>();
			Statement s = conn.createStatement();
			s.executeQuery("SELECT id FROM sadna.posts WHERE topicID='"+topicId+"'");
			ResultSet rs = s.getResultSet();
			while (rs.next())
			{	
				int postId = rs.getInt ("id");
				PostDataInt pd = getPostData(forumId, topicId, postId);
				ans.add(pd);
			}
			rs.close ();
			s.close ();
		return ans;
		
		}
		catch(SQLException e){throw new SQLException();}
	}
	
	
	@Override
	public LinkedList<TopicDataInt> getTopics(int forumId) throws SQLException {
		
		try{
		LinkedList<TopicDataInt> ans = new LinkedList<TopicDataInt>();
			Statement s = conn.createStatement();
			s.executeQuery("SELECT id, title, creationTime, creator, forumId FROM sadna.topics WHERE forumId='"+forumId+"'");
			ResultSet rs = s.getResultSet();
			while (rs.next())
			{	
				// get the topic data from the database.
				int topicId = rs.getInt ("id");
				String topicTitle = rs.getString ("title");
				Date titleCreationTime = rs.getDate("creationTime");
				String titleCreatorUserName = rs.getString ("creator");
				
				// get the topic creator from the database.
				Statement sCreator = conn.createStatement();
				sCreator.executeQuery("SELECT id, userName, email, firstName, lastName FROM sadna.members WHERE userName ='"+titleCreatorUserName+"'");
				ResultSet rsCreator = sCreator.getResultSet();
				rsCreator.first();
				int creatorId = rsCreator.getInt ("id");
				String creatorEmail = rsCreator.getString ("email");
				String creatorfName = rsCreator.getString ("firstName");
				String creatorlName = rsCreator.getString ("lastName");
				
				// creating the data that should be return to the client.
				MemberDataInt titleCreator = new MemberData(creatorId, titleCreatorUserName, creatorEmail, creatorfName, creatorlName);
				TopicDataInt td = new TopicData(topicId, titleCreationTime, topicTitle, titleCreator);
				ans.add(td); // adding the topic to the ans.
				
				rsCreator.close();
				sCreator.close();
			}
			rs.close ();
			s.close ();
		return ans;
		
		}
		catch(SQLException e){throw new SQLException();}
	}

	@Override
	public void removeForum(int forumId) throws SQLException {
			
		try
		{
		PreparedStatement s;
			s = conn.prepareStatement ("DELETE FROM `sadna`.`forums` WHERE `id`=?");
			s.setInt(1, forumId);
			s.executeUpdate ();
			s.close();	
		}
		catch(SQLException e){throw new SQLException();}
	}

	@Override
	public void removeFriend(String userName, String friendUserName) throws SQLException {
			
		try{
		PreparedStatement s;
			s = conn.prepareStatement ("DELETE FROM `sadna`.`friends` WHERE `username`=? AND `friends_username`=?");
			s.setString(1, userName);
			s.setString(2, friendUserName);
			s.executeUpdate ();
			s.close();
		}
		catch(SQLException e){throw new SQLException();}
	}

	@Override
	public void removeMember(String userName) throws SQLException {
		
		try{
		PreparedStatement s;
			s = conn.prepareStatement ("DELETE FROM `sadna`.`members` WHERE `userName`=?");
			s.setString(1, userName);
			s.executeUpdate ();
			s.close();	
		}
		catch(SQLException e){throw new SQLException();}
	}

	@Override
	public void removePost(int forumId, int topicId, int postId) throws SQLException {
		try{
		PreparedStatement s;
			s = conn.prepareStatement ("DELETE FROM `sadna`.`posts` WHERE `id`=?");
			s.setInt(1, postId);
			s.executeUpdate ();
			s.close();
		}
		catch(SQLException e){throw new SQLException();}
	}

	@Override
	public void removeTopic(int forumId, int topicId) throws SQLException {
		try
		{
		PreparedStatement s;
			s = conn.prepareStatement ("DELETE FROM `sadna`.`topics` WHERE `id`=?");
			s.setInt(1, topicId);
			s.executeUpdate ();
			s.close();	
		}
		catch(SQLException e){throw new SQLException();}
	}

	@Override
	public boolean forumExist(int forumId) throws SQLException {
		try{
		Statement s;
		boolean ans = false;
			s = conn.createStatement();
			s.executeQuery ("SELECT `id` FROM `sadna`.`forums` WHERE `id`="+forumId);
			ResultSet rs = s.getResultSet();
			ans =  rs.first(); // true if there are rows and false if there are no rows.
			rs.close();
			s.close ();	   
		return ans;
		}
		catch(SQLException e){throw new SQLException();}
	}

	@Override
	public boolean postExist(int forumId, int topicId, int postId) throws SQLException {
		try{
		Statement s;
		boolean ans = false;
			s = conn.createStatement();
			s.executeQuery ("SELECT `id` FROM `sadna`.`posts` WHERE `id`="+postId);
			ResultSet rs = s.getResultSet();
			ans =  rs.first(); // true if there are rows and false if there are no rows.
			rs.close();
			s.close ();	   
		return ans;
		}
		catch(SQLException e){throw new SQLException();}
	}

	@Override
	public boolean topicExist(int forumId, int topicId) throws SQLException {
		try{
		Statement s;
		boolean ans = false;
			s = conn.createStatement();
			s.executeQuery ("SELECT `id` FROM `sadna`.`topics` WHERE `id`="+topicId);
			ResultSet rs = s.getResultSet();
			ans =  rs.first(); // true if there are rows and false if there are no rows.
			rs.close();
			s.close ();	   
		return ans;
		}
		catch(SQLException e){throw new SQLException();}
	}

	@Override
	public boolean userExist(String userName) throws SQLException {
		try{
		Statement s;
		boolean ans = false;
			s = conn.createStatement();
			s.executeQuery ("SELECT `id` FROM `sadna`.`members` WHERE `userName`='"+userName+"'");
			ResultSet rs = s.getResultSet();
			ans =  rs.first(); // true if there are rows and false if there are no rows.
			rs.close();
			s.close ();	   
		return ans;
		}
		catch(SQLException e){throw new SQLException();}
	}

	@Override
	public boolean isCreatorOfPost(int forumId, int topicId, int PostId, String userName) throws ForumNotFoundException,
			TopicNotFoundException, PostNotExistException, SQLException {
		
		try{
		boolean ans = false;
			Statement s = conn.createStatement();
			ResultSet rs = s.executeQuery("SELECT creator FROM sadna.posts WHERE id='"+PostId+"' AND creator='"+userName+"'");
		
			ans = rs.first();
		return ans;
		}
		catch(SQLException e){throw new SQLException();}
	}

	@Override
	public ForumDataInt getForumData(int forumId) throws SQLException {
		try
		{
		ForumDataInt fd = null;
			Statement s = conn.createStatement();
			s.executeQuery("SELECT id, name, creator FROM sadna.forums WHERE id='"+forumId+"'");
			ResultSet rs = s.getResultSet();
			rs.first();
			String forumName = rs.getString ("name");
			String creatorUserName = rs.getString ("creator");
			MemberDataInt creator = getMemberDataByUserName(creatorUserName);
			fd = new ForumData(forumId, forumName, creator);	
			rs.close ();
			s.close ();
		return fd;
		}
		catch(SQLException e){throw new SQLException();}
	}

	@Override
	public PostDataInt getPostData(int forumId, int TopicId, int postId) throws SQLException {
		//LinkedList<PostDataInt> ans = new LinkedList<PostDataInt>();
		
		try{
		PostDataInt pd = null;
			Statement s = conn.createStatement();
			s.executeQuery("SELECT id, title, content, creationTime, editingTime, creator FROM sadna.posts WHERE id='"+postId+"' and topicID='"+TopicId+"'");
			ResultSet rs = s.getResultSet();
			rs.first();
				int pId = rs.getInt ("id");
				String title = rs.getString ("title");
				String content = rs.getString ("content");
				Date creationTime = rs.getTimestamp("creationTime");
				Date editingTime = rs.getTimestamp("editingTime");
				String creatorUserName = rs.getString("creator");
				//System.out.println("was here0");
				Statement sCreator = conn.createStatement();
				sCreator.executeQuery("SELECT id, userName, email, firstName, lastName FROM sadna.members WHERE userName ='"+creatorUserName+"'");
				//System.out.println("was here1");
				ResultSet rsCreator = sCreator.getResultSet();
				rsCreator.first();
				//System.out.println(rsCreator.first());
				//System.out.println("was here2");
				int creatorId = rsCreator.getInt ("id");
				String creatorEmail = rsCreator.getString ("email");
				String creatorfName = rsCreator.getString ("firstName");
				String creatorlName = rsCreator.getString ("lastName");
				//System.out.println("was here3");
				//System.out.println(creatorId+" "+creatorEmail+" "+creatorfName+" "+creatorlName);
				MemberDataInt creator = new MemberData(creatorId, creatorUserName, creatorEmail, creatorfName, creatorlName);
				pd = new PostData(pId, title, content, creationTime, editingTime, creator);
				rsCreator.close();
				sCreator.close();
			rs.close ();
			s.close ();
		return pd;
		}
		catch(SQLException e){throw new SQLException();}
	}
	
	
	@Override
	public String getTopicCreator(int forumId, int topicId) throws SQLException {
		try{
		String titleCreatorUserName = null;
			Statement s = conn.createStatement();
			s.executeQuery("SELECT id, title, creationTime, creator, forumId FROM sadna.topics WHERE id='"+topicId+"'");
			ResultSet rs = s.getResultSet();
			rs.first();
			titleCreatorUserName = rs.getString ("creator");
			rs.close ();
			s.close ();
		return titleCreatorUserName;
		}
		catch(SQLException e){throw new SQLException();}
	}
	
	private MemberDataInt getMemberDataByUserName(String userName) throws SQLException{
		try{
		Statement s;
		MemberDataInt member = null;
			s = conn.createStatement();
			s.executeQuery("SELECT id, userName, email, firstName, lastName FROM sadna.members WHERE userName ='"+userName+"'");
			ResultSet rs = s.getResultSet();
			rs.first();
			int creatorId = rs.getInt ("id");
			String creatorEmail = rs.getString ("email");
			String creatorfName = rs.getString ("firstName");
			String creatorlName = rs.getString ("lastName");	
			// creating the data that should be return to the client.
			member = new MemberData(creatorId, userName, creatorEmail, creatorfName, creatorlName);
		return member;
		}
		catch(SQLException e){throw new SQLException();}
	}
	
	@Override
	public TopicDataInt getTopicData(int forumId, int topicId) throws SQLException {
		try{
		TopicDataInt td = null;
			Statement s = conn.createStatement();
			s.executeQuery("SELECT id, title, creationTime, creator, forumId FROM sadna.topics WHERE id='"+topicId+"'");
			ResultSet rs = s.getResultSet();
			// get the topic data from the database.
			rs.first();
			String topicTitle = rs.getString ("title");
			Date titleCreationTime = rs.getDate("creationTime");
			String titleCreatorUserName = rs.getString ("creator");
			// get the topic creator from the database.
			MemberDataInt titleCreator = getMemberDataByUserName(titleCreatorUserName);	
			// creating the data that should be return to the client.
			td = new TopicData(topicId, titleCreationTime, topicTitle, titleCreator);
			rs.close ();
			s.close ();
		return td;
		}
		catch(SQLException e){throw new SQLException();}
	}
}
